package Demo;

import bean.Course;
import bean.Student;
import bean.Teacher;
import bean.User;

import java.sql.*;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 34255
 * Date: 2024-09-15
 * Time: 9:30
 */
public class JDBC {
    public static Scanner input = new Scanner(System.in);

    //构建连接参数
    public static String url = "jdbc:mysql://localhost:3306/学生选课系统";
    public static String consumer = "root";
    public static String password = "root";

    public static int jdbcDemo(User user) throws Exception {
        int type = user.getType();
        if(type > 2 || type < 0) return -1;

        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        String[] str = new String[]{"管理员","学生","教师"};

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select * from " + str[type] + " where 账号 = ? and 密码 = ?";

        //获取执行sql语句PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());
        stat.setString(2, user.getPassword());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        if(set.next()) return type;

        //释放资源
        set.close();
        stat.close();
        conn.close();
        return -1;
    }

    public static void alterPassWord(User user,String type,String newPassWord) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "update " + type +" set 密码 = ? where 账号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setString(1, newPassWord);
        stat.setInt(2, user.getAccountNum());

        //执行sql语句
        int count = stat.executeUpdate();
        if (count == 0) {
            System.out.println("修改密码失败!");
            return;
        }
        //关闭资源
        stat.close();
        conn.close();
        System.out.println("修改密码成功!");
    }

    public static void queryStuCourse(User user) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select `课程名`,`教学地点`,`教学时间`,(select 姓名 from 教师 where 账号 = 教师账号) as 教学老师,`学分`,`课程状态` from 课程 where 课程代号 in (select 课程代号 from 选修 where 学生账号 = ?)";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************您的课程如下：******************");
        int i = 1;
        while(set.next()){
            String name = set.getString(1);
            String classroom = set.getString(2);
            String time = set.getString(3);
            String teacher = set.getString(4);
            int credit = set.getInt(5);
            String state = set.getString(6);
            System.out.println("课程 " + i++ + " {" +
                    "课程名：" + name +
                    "  教学地点：" + classroom +
                    "  教学时间：" + time +
                    "  教学老师：" + teacher +
                    "  学分：" + credit +
                    "  教学状态：" + state +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void queryScore(User user) throws  Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select 课程名,选修.`成绩`,学分,课程状态 from 课程 JOIN 选修 ON 选修.`课程代号` = 课程.`课程代号` WHERE `学生账号` = ? and 课程状态 = '已结课';";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************您的已结课课程成绩如下：******************");
        int i = 1;
        while(set.next()){
            String name = set.getString(1);
            Double score = set.getDouble(2);
            int credit = set.getInt(3);
            String state = set.getString(4);
            System.out.println("成绩: " +
                    "课程名：" + name +
                    "  成绩：" + score +
                    "  学分：" + credit +
                    "  教学状态：" + state
            );
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void queryStudentInfo(User user) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select 账号,姓名,年龄,性别,院系,年级,专业 from 学生 JOIN 院系 ON 学生.`班级号` = 院系.`班级号` where 账号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************您的个人信息如下：******************");
        while(set.next()){
            int accountNum = set.getInt(1);
            String name = set.getString(2);
            int age = set.getInt(3);
            String gender = set.getString(4);
            String department = set.getString(5);
            int grade = set.getInt(6);
            String speciality = set.getString(7);
            System.out.println("个人信息: " +
                    "账号：" + accountNum +
                    "  姓名：" + name +
                    "  年龄：" + age +
                    "  性别：" + gender +
                    "  院系：" + department +
                    "  年级：" + grade +
                    "  专业：" + speciality
                    );
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void queryTeacherInfo(User user) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select 账号,姓名,性别,年龄,职称,联系方式 from 教师 where 账号 = ?;";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************您的个人信息如下：******************");
        while(set.next()){
            int accountNum = set.getInt(1);
            String name = set.getString(2);
            String gender = set.getString(3);
            int age = set.getInt(4);
            String rank = set.getString(5);
            long phone = set.getLong(6);
            System.out.println("个人信息: " +
                    "账号：" + accountNum +
                    "  姓名：" + name +
                    "  性别：" + gender +
                    "  年龄：" + age +
                    "  职称：" + rank +
                    "  联系电话：" + phone
            );
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void queryCourse(User user) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select 课程.* from 教师 JOIN 课程 ON 教师.`账号` = 课程.`教师账号` where 教师.`账号` = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************您的教学课程信息如下：******************");
        while(set.next()){
            int code = set.getInt(1);
            String name = set.getString(2);
            String classroom = set.getString(3);
            String time = set.getString(4);
            int credit = set.getInt(5);
            int teacherNum = set.getInt(6);
            String state = set.getString(7);
            int i = 1;
            System.out.println("课程 " + i++ + " {" +
                    "课程代号 " + code +
                    "  课程名：" + name +
                    "  教学地点：" + classroom +
                    "  教学时间：" + time +
                    "  学分：" + credit +
                    "  教师账号：" + teacherNum +
                    "  课程状态：" + state +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void chooseCourse(User user) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select * from 课程 where 课程代号 NOT IN (select 课程代号 from 选修 where 学生账号 = ?) and 课程状态 = '未结课';";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);
        stat.setInt(1,user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************可以选择的课程如下：******************");
        int i = 1;
        while(set.next()){
            int code = set.getInt(1);
            String name = set.getString(2);
            String classroom = set.getString(3);
            String time = set.getString(4);
            int credit = set.getInt(5);
            int teacherNum = set.getInt(6);
            String state = set.getString(7);
            System.out.println("课程 " + i++ + " {" +
                    "课程代号 " + code +
                    "  课程名：" + name +
                    "  教学地点：" + classroom +
                    "  教学时间：" + time +
                    "  学分：" + credit +
                    "  老师账号：" + teacherNum +
                    "  教学状态：" + state +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();

        System.out.println("请输入您要选修的课程代号");
        int code = input.nextInt();
        //校验
        sql = "INSERT INTO `选修` (`学生账号`,`课程代号`) VALUES (?,?);";
        stat = conn.prepareStatement(sql);

        stat.setInt(1,user.getAccountNum());
        stat.setInt(2,code);

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("选课成功");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void exitCourse(User user) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select 课程.* from 课程 JOIN 选修 ON 课程.`课程代号` = 选修.`课程代号` WHERE 选修.`学生账号` = ? and 课程状态 = '未结课';";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        stat.setInt(1,user.getAccountNum());
        ResultSet set = stat.executeQuery();

        //执行sql语句
        System.out.println();
        System.out.println("***************可以退掉的选修课程如下：******************");
        int i = 1;
        while(set.next()){
            int code = set.getInt(1);
            String name = set.getString(2);
            String classroom = set.getString(3);
            String time = set.getString(4);
            int credit = set.getInt(5);
            int teacherNum = set.getInt(6);
            String state = set.getString(7);
            System.out.println("课程 " + i++ + " {" +
                    "课程代号 " + code +
                    "  课程名：" + name +
                    "  教学地点：" + classroom +
                    "  教学时间：" + time +
                    "  学分：" + credit +
                    "  老师账号：" + teacherNum +
                    "  教学状态：" + state +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();

        System.out.println("请输入您要退掉的课程的代号");
        int code = input.nextInt();
        //校验
        sql = "DELETE from 选修 where 选修.`学生账号` = ? and 选修.`课程代号` = ?;";
        stat = conn.prepareStatement(sql);

        stat.setInt(1,user.getAccountNum());
        stat.setInt(2,code);

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("退课成功");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void queryCourseScore(User user) throws  Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select `课程`.* from 课程 JOIN 教师 ON 教师.`账号` = 课程.`教师账号` where 课程状态 = '已结课' and 教师.`账号` = ?;";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************课程信息如下：******************");
        int i = 1;
        while(set.next()){
            int code = set.getInt(1);
            String name = set.getString(2);
            String classroom = set.getString(3);
            String time = set.getString(4);
            int teacherNum = set.getInt(5);
            int credit = set.getInt(6);
            String state = set.getString(7);
            System.out.println("课程 " + i++ + " {" +
                    "课程代号 " + code +
                    "  课程名：" + name +
                    "  教学地点：" + classroom +
                    "  教学时间：" + time +
                    "  学分：" + credit +
                    "  教师账号：" + teacherNum +
                    "  课程状态：" + state +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();
        System.out.println("请选择输入您要查看的课程成绩的课程代码");
        int code = input.nextInt();
        sql = "SELECT 账号,姓名,(SELECT 课程名 from 课程 where 课程代号 = ?) 课程名称,成绩 from 选修 JOIN 学生 ON 学生.`账号` = 选修.`学生账号` and 课程代号 = ?;";

        //获取PreparedStatement对象
        stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, code);
        stat.setInt(2, code);

        //执行sql语句
        set = stat.executeQuery();
        System.out.println();
        System.out.println("***************课程信息如下：******************");
        i = 1;
        while(set.next()){
            int accountNum = set.getInt(1);
            String name = set.getString(2);
            String className = set.getString(3);
            double score = set.getDouble(4);
            System.out.println("成绩 " + i++ + " {" +
                    "学生账号 " + accountNum +
                    "  姓名：" + name +
                    "  课程名称：" + className +
                    "  成绩：" + score +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();

        //关闭资源
        stat.close();
        conn.close();
    }
    public static void alterStudentScore(User user) throws  Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select 课程.* from 教师 JOIN 课程 ON 教师.`账号` = 课程.`教师账号` where 教师.`账号` = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************课程信息如下：******************");
        int i = 1;
        while(set.next()){
            int code = set.getInt(1);
            String name = set.getString(2);
            String classroom = set.getString(3);
            String time = set.getString(4);
            int teacherNum = set.getInt(5);
            int credit = set.getInt(6);
            String state = set.getString(7);
            System.out.println("课程 " + i++ + " {" +
                    "课程代号 " + code +
                    "  课程名：" + name +
                    "  教学地点：" + classroom +
                    "  教学时间：" + time +
                    "  学分：" + credit +
                    "  教师账号：" + teacherNum +
                    "  课程状态：" + state +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();
        System.out.println("请选择您要修改的哪门课程的课程");
        int code = input.nextInt();
        sql = "SELECT 账号,姓名,(SELECT 课程名 from 课程 where 课程代号 = ?) 课程名称,成绩 from 选修 JOIN 学生 ON 学生.`账号` = 选修.`学生账号` and 课程代号 = ?;";

        //获取PreparedStatement对象
        stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, code);
        stat.setInt(2, code);

        //执行sql语句
        set = stat.executeQuery();
        System.out.println();
        System.out.println("***************课程信息如下：******************");
        i = 1;
        while(set.next()){
            int accountNum = set.getInt(1);
            String name = set.getString(2);
            String className = set.getString(3);
            double score = set.getDouble(4);
            System.out.println("成绩 " + i++ + " {" +
                    "学生账号 " + accountNum +
                    "  姓名：" + name +
                    "  课程名称：" + className +
                    "  成绩：" + score +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();
        System.out.println("请输入要修改的学生的成绩所对应的学生账号");
        int accountNum = input.nextInt();
        System.out.println("请输入修改后的成绩");
        double newScore = input.nextDouble();
        sql = "UPDATE 选修 set 成绩 = ? where 课程代号 = ? and 学生账号 = ?";

        //获取PreparedStatement对象
        stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setDouble(1, newScore);
        stat.setInt(2, code);
        stat.setInt(3, accountNum);

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("修改成绩成功");
        }

        //关闭资源
        stat.close();
        conn.close();
    }

    public static void addStudent(Student student) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "INSERT INTO `学生` (`姓名`,`性别`,`年龄`,`班级号`) VALUES (?,?,?,?)";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setString(1, student.getName());
        stat.setString(2, student.getGender());
        stat.setInt(3, student.getAge());
        stat.setInt(4, student.getClassNum());

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("新建学生成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void dropStudent(Student student) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "delete from 学生 where 账号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, student.getAccountNum());

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("删除学生成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void alterStudent(Student student) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "update 学生 set 姓名 = ?,性别 = ?,年龄 = ?,班级号 = ? where 账号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setString(1, student.getName());
        stat.setString(2, student.getGender());
        stat.setInt(3, student.getAge());
        stat.setInt(4, student.getClassNum());
        stat.setInt(5, student.getAccountNum());
        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("更新学生信息成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void selectStudent() throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select 账号,姓名,密码,年龄,性别,院系,年级,专业 from 学生 JOIN 院系 ON 学生.`班级号` = 院系.`班级号`;";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************学生信息如下：******************");
        int i = 1;
        while(set.next()){
            int accountNum = set.getInt(1);
            String name = set.getString(2);
            String passWord = set.getString(3);
            int age = set.getInt(4);
            String gender = set.getString(5);
            String department = set.getString(6);
            int grade = set.getInt(7);
            String major = set.getString(8);
            System.out.println("学生 " + i++ + " {" +
                    "学生账号： " + accountNum +
                    "  姓名：" + name +
                    "  密码：" + passWord +
                    "  年龄：" + age +
                    "  性别：" + gender +
                    "  院系：" + department +
                    "  年级：" + grade +
                    "  专业：" + major +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void addTeacher(Teacher teacher) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "INSERT INTO `教师` (`姓名`,`性别`,`年龄`,`职称`,`联系方式`) VALUES (?,?,?,?,?)";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setString(1, teacher.getName());
        stat.setString(2, teacher.getGender());
        stat.setInt(3, teacher.getAge());
        stat.setString(4, teacher.getRank());
        stat.setLong(5, teacher.getPhoneNum());

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("新建教师成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void dropTeacher(Teacher teacher) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "delete from 教师 where 账号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, teacher.getAccountNum());

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("删除教师成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void alterTeacher(Teacher teacher) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "update 教师 set 姓名 = ?,性别 = ?,年龄 = ?,职称 = ?,联系方式 = ? where 账号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setString(1, teacher.getName());
        stat.setString(2, teacher.getGender());
        stat.setInt(3, teacher.getAge());
        stat.setString(4, teacher.getRank());
        stat.setLong(5, teacher.getPhoneNum());
        stat.setInt(6,teacher.getAccountNum());
        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("更新教师信息成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void selectTeacher() throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "SELECT * from 教师;";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************教师信息如下：******************");
        int i = 1;
        while(set.next()){
            int accountNum = set.getInt(1);
            String name = set.getString(2);
            String gender = set.getString(3);
            int age = set.getInt(4);
            String rank = set.getString(5);
            String passWord = set.getString(6);
            long phone = set.getLong(7);
            System.out.println("教师 " + i++ + " {" +
                    "教师账号： " + accountNum +
                    "  姓名：" + name +
                    "  性别：" + gender +
                    "  年龄：" + age +
                    "  职称：" + rank +
                    "  密码：" + passWord +
                    "  联系方式：" + phone +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void addTCourse(Course course) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "INSERT INTO `课程` (`课程名`,`教学地点`,`教学时间`,`学分`,`教师账号`) VALUES (?,?,?,?,?)";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setString(1, course.getName());
        stat.setString(2, course.getClassroom());
        stat.setString(3, course.getTime());
        stat.setInt(4,course.getCredit());
        stat.setInt(5, course.getTeacherAccountNum());

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("新建课程成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void dropCourse(Course course) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "delete from 课程 where 课程代号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, course.getCode());

        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("删除课程成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void alterCourse(Course course) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "update 课程 set 课程名 = ?,教学地点 = ?,教学时间 = ?,学分 = ?,教师账号 = ? where 课程代号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setString(1, course.getName());
        stat.setString(2, course.getClassroom());
        stat.setString(3, course.getTime());
        stat.setInt(4,course.getCredit());
        stat.setInt(5, course.getTeacherAccountNum());
        stat.setInt(6, course.getCode());
        //执行sql语句
        int count = stat.executeUpdate();
        if(count != 0){
            System.out.println("更新课程信息成功!");
        }
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void selectCourse() throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "select 课程代号,课程名,教学地点,教学时间,学分,教师.`姓名`,课程状态 from 课程 JOIN 教师 ON 教师.`账号` = 课程.`教师账号`;";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************课程信息如下：******************");
        int i = 1;
        while(set.next()){
            int code = set.getInt(1);
            String name = set.getString(2);
            String classroom = set.getString(3);
            String time = set.getString(4);
            int credit = set.getInt(5);
            String teacher = set.getString(6);
            String state = set.getString(7);
            System.out.println("课程 " + i++ + " {" +
                    "课程代号 " + code +
                    "  课程名：" + name +
                    "  教学地点：" + classroom +
                    "  教学时间：" + time +
                    "  学分：" + credit +
                    "  教师：" + teacher +
                    "  课程状态：" + state +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }

    public static void queryStudentChooseCourseInfo(User user) throws Exception{
        //获取数据库连接：将java代码与指定数据库构建联系
        Connection conn = DriverManager.getConnection(url, consumer, password);

        //预编译sql语句,用?占位符替代要传递的参数
        String sql = "SELECT 课程代号,课程名,教学地点,教学时间,学分,课程状态 from 课程 where 教师账号 = ?";

        //获取PreparedStatement对象
        PreparedStatement stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1, user.getAccountNum());

        //执行sql语句
        ResultSet set = stat.executeQuery();
        System.out.println();
        System.out.println("***************课程信息如下：******************");
        int i = 1;
        while(set.next()){
            int code = set.getInt(1);
            String name = set.getString(2);
            String classroom = set.getString(3);
            String time = set.getString(4);
            int credit = set.getInt(5);
            String state = set.getString(6);
            System.out.println("课程 " + i++ + " {" +
                    "课程代号 " + code +
                    "  课程名：" + name +
                    "  教学地点：" + classroom +
                    "  教学时间：" + time +
                    "  学分：" + credit +
                    "  课程状态：" + state +
                    '}');
        }
        System.out.println("********************************************");
        System.out.println();

        System.out.println("请输入您要查看的课程信息的课程代号");
        int code = input.nextInt();


        //预编译sql语句,用?占位符替代要传递的参数
        sql = "SELECT 账号,姓名,院系.*,(SELECT 课程名 from 课程 where 课程代号 = ?) 课程名称 from 学生 JOIN 院系 ON 学生.`班级号` = 院系.`班级号` WHERE 账号 in (SELECT 学生账号 from 选修 WHERE `课程代号` = ?);";

        //获取PreparedStatement对象
        stat = conn.prepareStatement(sql);

        //设置?参数
        stat.setInt(1,code);
        stat.setInt(2,code);

        //执行sql语句
        set = stat.executeQuery();
        System.out.println();
        System.out.println("***************学生信息如下：******************");
        while(set.next()){
            int accountNum = set.getInt(1);
            String name = set.getString(2);
            int classNum = set.getInt(3);
            String department = set.getString(4);
            int grade = set.getInt(5);
            String speciality = set.getString(6);
            String className = set.getString(7);
            System.out.println("学生信息: " +
                    "账号：" + accountNum +
                    "  姓名：" + name +
                    "  班级号：" + classNum +
                    "  院系：" + department +
                    "  年级：" + grade +
                    "  专业：" + speciality +
                    "  课程名称：" + className
            );
        }
        System.out.println("********************************************");
        System.out.println();
        //关闭资源
        stat.close();
        conn.close();
    }
}
